class Question < ActiveRecord::Base
  
  #TODO stage: dabar 'Svarstymas' 'Priėmimas' bet buna ir 'grazinta ....' http://www3.lrs.lt/pls/inter/w5_sale.klaus_stadija?p_svarst_kl_stad_id=-2675

  belongs_to :sitting
  has_many :votes 
  has_many :political_group_votes
  has_many :speeches

  def self.find_questions_with_rebellion(min_rebellion)
    sql=<<-SQL
    select
    question_id,
    sum(
      case parliament_rebellion
        when true then 1
        when false then 0
      end
    ) as uz,
    sum(
      case parliament_rebellion
        when true then 0
        when false then 1
      end
    ) as pries

    from questions, votes
    where question_id = questions.id and parliament_rebellion is not null group by question_id
    SQL
    questions = Array.new
    r = ActiveRecord::Base.connection.execute(sql)
    while row = r.fetch_row do
      qid = row[0].to_i
      aye = row[1].to_i
      nay = row[2].to_i
      min = aye <= nay ? aye : nay
      max = aye >= nay ? aye : nay
      questions << qid if min_rebellion <= min.to_f / (max + min)
    end
    questions
  end

  def self.find_questions_with_speeches(min_speeches)
    sql=<<-SQL
    select
      questions.id, count(speeches.id)
    from questions
      left join speeches on questions.id = question_id
    group by questions.id
    SQL
    collect_questions(sql, min_speeches)
  end
  
  def self.find_questions_with_attendances(min_attendances)
    sql=<<-SQL
    select 
    questions.id,
    sum(
      case present
        when true then 1
        when false then 0
      end
    ) as sp
    from questions, attendances, sittings
    where questions.sitting_id = sittings.id and attendances.sitting_id = sittings.id
    group by questions.id
    SQL
    collect_questions(sql, min_attendances)
  end

  
  def self.find_questions_with_votes(min_votes)
    sql=<<-SQL
    select question_id,
        sum(
        case action
          when 1 then 0
          when 2 then 0
          when 3 then 1
          when 4 then 1
          when 5 then 1
        end
        ) / count(*) as sant
    from votes group by question_id
    SQL
    collect_questions(sql, min_votes)
  end
  
private 

  def self.collect_questions(sql, rule)
    questions = Array.new
    r = ActiveRecord::Base.connection.execute(sql)
    while row = r.fetch_row do
      questions << row[0].to_i if rule <= row[1].to_f
    end
    questions
  end
end
